package org.mpremote.android.framework.database;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;
import org.mpremote.android.framework.xml.Album;
import org.mpremote.android.framework.xml.Artist;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by IntelliJ IDEA.
 * User: Gilad
 * Date: 01/10/2010
 * Time: 18:25:40
 */
public class MPMusicDatabaseHelper extends SQLiteOpenHelper implements MPDatabaseConstants {

    private SQLiteStatement artistsInsertStatement;
    private SQLiteStatement albumsInsertStatement;


    protected MPMusicDatabaseHelper(Context context) {
        super(context, MUSIC_DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        Log.i(this.getClass().getSimpleName(), "Creating DB");
        sqLiteDatabase.execSQL(CREATE_ARTISTS_TABLE);
        sqLiteDatabase.execSQL(CREATE_ALBUMS_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        Log.i(this.getClass().getSimpleName(), "Upgrading DB");
        recreateDatabase();
        onCreate(sqLiteDatabase);
    }

    public void insertArtists(List<Artist> artists) {
        if (artistsInsertStatement == null) {
            final SQLiteDatabase readableDatabase = getReadableDatabase();
            artistsInsertStatement = readableDatabase.compileStatement(ARTISTS_INSERT);
        }
        final SQLiteDatabase writableDatabase = getWritableDatabase();
        writableDatabase.execSQL("DELETE FROM " + ARTISTS_TABLE_NAME);
        for (Artist artist : artists) {
            artistsInsertStatement.bindString(1, artist.getName());
            artistsInsertStatement.bindString(2, artist.getAlbumsUrl());
            artistsInsertStatement.bindString(3, artist.getAddToPlaylistUrl());
            artistsInsertStatement.executeInsert();
            artistsInsertStatement.clearBindings();
        }
    }

    public void insertAlbums(List<Album> albumList) {
        if (albumsInsertStatement == null) {
            final SQLiteDatabase readableDatabase = getReadableDatabase();
            albumsInsertStatement = readableDatabase.compileStatement(ALBUMS_INSERT);

        }
        final SQLiteDatabase writableDatabase = getWritableDatabase();
        writableDatabase.execSQL("DELETE FROM " + ALBUM_TABLE_NAME);
        for (Album album : albumList) {
            albumsInsertStatement.bindString(1, album.getName());
            albumsInsertStatement.bindString(2, album.getYear());
            albumsInsertStatement.bindString(3, album.getImageUrl());
            albumsInsertStatement.bindString(4, album.getTracksUrl());
            albumsInsertStatement.bindString(5, album.getAddToPlaylistUrl());
            albumsInsertStatement.executeInsert();
            albumsInsertStatement.clearBindings();
        }
    }

    public List<Artist> getAllArtists() {
        final SQLiteDatabase readableDatabase = getReadableDatabase();
        List<Artist> results = new ArrayList<Artist>();
        Cursor cursor = readableDatabase.query(ARTISTS_TABLE_NAME, new String[]{"name, albums_url, playlist_add_url"}, null, null, null, null, null);
        if (cursor.moveToFirst()) {
            do {
                Artist artist = new Artist(cursor.getString(0), cursor.getString(1), cursor.getString(2));
                results.add(artist);
            } while (cursor.moveToNext());
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        return results;
    }

    public List<Album> getAllAlbums() {
        final SQLiteDatabase readableDatabase = getReadableDatabase();
        List<Album> results = new ArrayList<Album>();
        Cursor cursor = readableDatabase.query(ALBUM_TABLE_NAME, new String[]{"name, year, image_url, tracks_url, playlist_add_url"}, null, null, null, null, null);
        if (cursor.moveToFirst()) {
            do {
                Album artist = new Album(cursor.getString(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4));
                results.add(artist);
            } while (cursor.moveToNext());
        }
        if (!cursor.isClosed()) {
            cursor.close();
        }
        return results;
    }

    public Artist getArtist(String artistName) {
        final SQLiteDatabase readableDatabase = getReadableDatabase();
        final Cursor cursor = readableDatabase.rawQuery("SELECT * FROM " + ARTISTS_TABLE_NAME + " WHERE name ='" + artistName + "'", null);
        cursor.moveToFirst();
        Artist artist = new Artist(cursor.getString(0), cursor.getString(1), cursor.getString(2));
        cursor.close();
        return artist;
    }

    public void recreateDatabase() {
        getWritableDatabase().execSQL("DROP TABLE " + ARTISTS_TABLE_NAME);
        getWritableDatabase().execSQL("DROP TABLE " + ALBUM_TABLE_NAME);
    }
}
